package homework;//使用德鲁伊连接池
//将德鲁伊连接池封装成一个方法


import JDBCLEARN.JdbcLearn;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Scanner;

public class HomeWorkStudentSystem {
    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
        JdbcLearn jdbcLearn = new JdbcLearn();
        jdbcLearn.insert();
    }

    //封装德鲁伊连接池方法
    public Connection link() throws Exception {
        //德鲁伊连接池
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JDBCLEARN/jdbc.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println("connection = " + connection);
        return connection;
    }



    @Test
    public void insert() throws Exception {
        //使用封装的德鲁伊连接池方法
        Connection connection = link();
        Student student = new Student();
        Scanner scanner = new Scanner(System.in);

        System.out.println("请输入要添加的学生姓名");
        String sname = scanner.next();
        System.out.println("请输入要添加的学生性别");
        String ssex = scanner.next();
        System.out.println("请输入要添加的学生年龄");
        String sage = String.valueOf(Integer.valueOf(scanner.next()));
        System.out.println("请输入要添加的学生专业");
        String sdepartment = scanner.next();


        student.setSname(sname);
        student.setSsex(ssex);
        student.setSage(Integer.valueOf(sage));
        student.setSdepartment(sdepartment);



        String sql="insert into student  values(null,?,?,?,?)";

        //4.创建数据操作对象
        PreparedStatement statement = connection.prepareStatement(sql);

        statement.setString(1,student.getSname());
        statement.setString(2,student.getSsex());
        statement.setInt(3,student.getSage());
        statement.setString(4,student.getSdepartment());

        //5.执行sql，获取结果集
        int i = statement.executeUpdate();
        System.out.println("向数据库中插入了i = " + i);

        //6. 关闭资源
        //关闭资源
        statement.close();

    }
    /**
     * 数据的查询
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void selectData() throws Exception {

        //使用封装的德鲁伊连接池方法
        Connection connection = link();

        ArrayList<Student> students = new ArrayList<>();




        //3.实现Crud
        String sql = "select * from student";
        //4.创建数据操作对象
        PreparedStatement statement = connection.prepareStatement(sql);
        //5.执行sql，获取结果集
        ResultSet resultSet = statement.executeQuery();

        //6.通过next指向结果集第一行
        while (resultSet.next()) {
            Student student = new Student();
            //7.解析结果中数据字段
            student.setSname(resultSet.getString("sname"));
            student.setSsex(resultSet.getString("ssex"));
            student.setSage(resultSet.getInt("sage"));
            student.setSdepartment(resultSet.getString("sdepartment"));
            students.add(student);


        }
        for (int i = 0; i < students.size(); i++) {
            System.out.println("students.get(i).getSname() = " + students.get(i).getSname());
            System.out.println("------------------");
        }
        //关闭资源
        statement.close();


    }

    /**
     * 删除某条记录
     * @throws SQLException
     * @throws ClassNotFoundException
     */

    @Test
    public void deleteDb() throws Exception {

        //使用封装的德鲁伊连接池方法
        Connection connection = link();


        Student student = new Student();
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要删除的学生姓名");
        String sname = scanner.next();


//3.定义sql
//拼接
        String sql="delete from student where sname=?";

        System.out.println("sql = " + sql);


        //4.创建数据操作对象
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,sname);

        //5.执行sql，获取结果集
        int i = statement.executeUpdate();
        System.out.println("删除数据库中 " + i);

        //6. 关闭资源
        //关闭资源
        statement.close();

    }



       @Test
    public void inserttest() throws Exception {
           //使用封装的德鲁伊连接池方法
           Connection connection = link();
           Student student = new Student();
           Scanner scanner = new Scanner(System.in);

           System.out.println("请输入要添加的学生姓名");
           String sname = scanner.next();
           System.out.println("请输入要添加的学生性别");
           String ssex = scanner.next();
           System.out.println("请输入要添加的学生年龄");
           String sage = String.valueOf(Integer.valueOf(scanner.next()));
           System.out.println("请输入要添加的学生专业");
           String sdepartment = scanner.next();


           student.setSname(sname);
           student.setSsex(ssex);
           student.setSage(Integer.valueOf(sage));
           student.setSdepartment(sdepartment);

        String sql="insert into student values(null,?,?,?,?)";

        //4.创建数据操作对象
           PreparedStatement statement = connection.prepareStatement(sql);

           statement.setString(1,student.getSname());
           statement.setString(2,student.getSsex());
           statement.setInt(3,student.getSage());
           statement.setString(4,student.getSdepartment());

        //5.执行sql，获取结果集
        int i = statement.executeUpdate();
        System.out.println("向数据库中插入了i = " + i);

        //6. 关闭资源
        //关闭资源
        statement.close();

    }
    public void logon() throws Exception {
        Connection connection = link();
        Student student = new Student();
        Scanner scanner = new Scanner(System.in);

        System.out.println("请输入用户名");
        String sname = scanner.next();
        System.out.println("请输入密码");
        String ssex = scanner.next();

        String sql = "select * from student where sname=? && ssex=?";
        //4.创建数据操作对象
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,sname);
        statement.setString(2, ssex);
        //5.执行sql，获取结果集
        ResultSet resultSet = statement.executeQuery();

        if (resultSet.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
            homework.HomeWorkStudentSystem homeWorkStudentTestSystem = new homework.HomeWorkStudentSystem();
            homeWorkStudentTestSystem.logon();
        }


    }
    public void we(){
        // STOPSHIP: 2024/7/17

    }

}
